﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Spread.Common.Features.Sample;
using GrapeCity.Spreadsheet;
using GrapeCity.CalcEngine;

namespace Spread.CellDataTypes
{
    public partial class Introduction : BaseSampleControl
    {
        private DataTable gradeTable;

        public Introduction()
        {
            InitializeComponent();
            fpSpread1.AllowCellOverflow = true;
            // enable Cell Data Types feature
            fpSpread1.AsWorkbook().WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;
            // enable enhanced excel keyboard shortcuts for Ctrl+Shift+F5 Show Card action
            fpSpread1.Features.ExcelCompatibleKeyboardShortcuts = true;
            InitCustomers();
            InitExternalVariable();
        }

        private void FormatAsTable(IWorksheet sheet, int row, int column, int row2, int column2)
        {
            // format as table
            ITable table = sheet.Tables.Add(row, column, row2, column2, YesNoGuess.Yes);
            // convert table to range
            FarPoint.Win.Spread.UndoRedo.ConvertTableToRangeUndoAction action = new FarPoint.Win.Spread.UndoRedo.ConvertTableToRangeUndoAction(fpSpread1, fpSpread1.ActiveSheet, table.Name);
            action.PerformUndoAction(fpSpread1.GetRootWorkbook());
        }
        private void InitCustomers()
        {
            IWorksheet sheet = fpSpread1.AsWorkbook().ActiveSheet;
            IStyles styles = sheet.Workbook.Styles; // workbook styles
            // create styles for cells
            IStyle titleStyle = styles.Add("title");
            titleStyle.IncludeFont = true;
            titleStyle.Font.Size = 14;
            titleStyle.Font.Bold = true;
            IStyle exampleTitleStyle = styles.Add("exampleTitle");
            exampleTitleStyle.Font.Bold = true;
            exampleTitleStyle.IncludeFont = true;
            sheet.Name = "Customers Vehicles";
            sheet.SetValue(5, 1, "Customer");
            sheet.SetValue(5, 2, "Email");
            sheet.SetValue(5, 3, "Vehicles");
            // format as table
            FormatAsTable(sheet, 5, 1, 8, 4);
            // merge cells for Vehicles column header
            sheet.Cells[5, 3, 5, 4].Merge();
            RichValue<Customer> customer1 = new RichValue<Customer>(GCCustomers.customers[0]) { ShowDetailsIcon = true };
            RichValue<Customer> customer2 = new RichValue<Customer>(GCCustomers.customers[1]) { ShowDetailsIcon = true };
            RichValue<Customer> customer3 = new RichValue<Customer>(GCCustomers.customers[2]) { ShowDetailsIcon = true };
            sheet.SetValue<RichValue<Customer>>(6, 1, new RichValue<Customer>[,] { { customer1 }, { customer2 }, { customer3 } });
            sheet.Cells[6, 2, 8, 2].Formula = "$B7.Email";
            sheet.Cells[6, 3, 8, 3].Formula2 = "TRANSPOSE($B7.Vehicles)";

            sheet.SetValue(10, 1, "Vehicle");
            // note that Vehicle implements IRichValue, so it doesn't need to use IRichValue<T>
            sheet.SetValue<Vehicle>(11, 1, new Vehicle[,] { { GCCustomers.customers[0].Vehicles[0] }, { GCCustomers.customers[0].Vehicles[1] }, { GCCustomers.customers[1].Vehicles[0] }, { GCCustomers.customers[1].Vehicles[1] }, { GCCustomers.customers[2].Vehicles[0] } });
            sheet.Tables.Add(10, 1, 15, 1, YesNoGuess.Yes);
            sheet.Columns["B:E"].AutoFit();
            sheet.SetValue(1, 1, "Examples of Custom Cell Data Types");
            sheet.Cells[1, 1].Style = titleStyle;
            sheet.SetValue(3, 1, "Table of Customer Vehicles");
            sheet.Cells[3, 1].Style = exampleTitleStyle;
            sheet.SetValue(10, 1, "Table of Vehicles");
            sheet.Cells[10, 1].Style = exampleTitleStyle;
            sheet.SetValue(17, 1, "Use Ctrl+Shift+F5 to invoke the ShowCard action for the cell data type, or click the glyph in the cell.");
            sheet.Cells[17, 1].ApplyStyle(BuiltInStyle.Note);
            sheet.SetValue(18, 1, "You can add more fields for Vehicle in the table using the drop-down list or using the data card.");
            sheet.Cells[18, 1].ApplyStyle(BuiltInStyle.Note);

        }
        private void InitExternalVariable()
        {
            IWorksheet sheet = fpSpread1.AsWorkbook().Worksheets.Add("ExternalVariable");
            IStyles styles = sheet.Workbook.Styles; // workbook styles
            IStyle exampleTitleStyle = styles["exampleTitle"];
            gradeTable = GetDataTable();
            DataTableVariable tableVar = new DataTableVariable(gradeTable, false, "Week 1");
            fpSpread1.AsWorkbook().Names.AddExternalVariable("gradeTable", tableVar);
            FormatAsTable(sheet, 4, 1, 19, 9);
            for (int c = 0; c < gradeTable.Columns.Count; c++)
                sheet.Cells[4, c + 1].Text = gradeTable.Columns[c].ColumnName;
            sheet.Cells["B6"].Formula2 = "gradeTable";
            sheet.Cells["C6"].Formula2 = "B6.[Week 2]";
            sheet.Cells["D6"].Formula2 = "B6.[Week 3]";
            sheet.Cells["E6"].Formula2 = "B6.[Week 4]";
            sheet.Cells["F6"].Formula2 = "B6.[Week 5]";
            sheet.Cells["G6"].Formula2 = "B6.[Week 6]";
            sheet.Cells["H6"].Formula2 = "B6.[Week 7]";
            sheet.Cells["I6"].Formula2 = "B6.[Subject]";
            sheet.Cells["J6"].Formula2 = "B6.[owner-subject]";

            DataTableVariable dynamicTableVar = new DataTableVariable(gradeTable, true);
            fpSpread1.AsWorkbook().Names.AddExternalVariable("gradeDynamicTable", dynamicTableVar);
            for (int c = 0; c < gradeTable.Columns.Count; c++)
                sheet.Cells[23, c + 1].Text = gradeTable.Columns[c].ColumnName;
            FormatAsTable(sheet, 23, 1, 38, 9);
            sheet.Cells["B25"].Formula2 = "gradeDynamicTable";
            sheet.Columns["B:J"].AutoFit();
            sheet.Cells["B3"].Text = "ExternalVariable \"gradeTable\" returns the DataTable as IRichValue -- dynamic array example below.";
            sheet.Cells["B3"].Style = exampleTitleStyle;
            sheet.Cells["B22"].Text = "ExternalVariable \"gradeDynamicTable\" returns the DataTable as a dynamic array.";
            sheet.Cells["B22"].Style = exampleTitleStyle;

        }
        private DataTable GetDataTable()
        {
            DataSet ds = new System.Data.DataSet();
            DataTable fpnames;
            DataTable fpclass;
            DataTable fpgrades;

            fpnames = ds.Tables.Add("Students");
            fpnames.Columns.AddRange(new DataColumn[] {new DataColumn("LastName", Type.GetType("System.String")), new DataColumn("FirstName",
            Type.GetType("System.String")), new DataColumn("id", Type.GetType("System.Int32"))});
            fpnames.Rows.Add(new Object[] { "Jane", "Mary", 0 });
            fpnames.Rows.Add(new Object[] { "Rodglet", "Jonnny", 1 });
            fpnames.Rows.Add(new Object[] { "Steris", "Pat", 2 });

            fpclass = ds.Tables.Add("Class");
            fpclass.Columns.AddRange(new DataColumn[] {new DataColumn("Subject", Type.GetType("System.String")), new DataColumn("owner",
            Type.GetType("System.Int32")), new DataColumn("owner-subject", Type.GetType("System.String"))});

            //First Student
            fpclass.Rows.Add(new Object[] { "Math", 0, "0-Math" });
            fpclass.Rows.Add(new Object[] { "English", 0, "0-English" });
            fpclass.Rows.Add(new Object[] { "History", 0, "0-History" });
            fpclass.Rows.Add(new Object[] { "Music", 0, "0-Music" });
            fpclass.Rows.Add(new Object[] { "Science", 0, "0-Science" });

            //Second Student
            fpclass.Rows.Add(new Object[] { "Math", 1, "1-Math" });
            fpclass.Rows.Add(new Object[] { "English", 1, "1-English" });
            fpclass.Rows.Add(new Object[] { "History", 1, "1-History" });
            fpclass.Rows.Add(new Object[] { "Music", 1, "1-Music" });
            fpclass.Rows.Add(new Object[] { "Science", 1, "1-Science" });

            //Third Student
            fpclass.Rows.Add(new Object[] { "Math", 2, "2-Math" });
            fpclass.Rows.Add(new Object[] { "English", 2, "2-English" });
            fpclass.Rows.Add(new Object[] { "History", 2, "2-History" });
            fpclass.Rows.Add(new Object[] { "Music", 2, "2-Music" });
            fpclass.Rows.Add(new Object[] { "Science", 2, "2-Science" });

            fpgrades = ds.Tables.Add("Grades");
            fpgrades.Columns.AddRange(new DataColumn[] {
                new DataColumn("Week 1", Type.GetType("System.String")),
                new DataColumn("Week 2", Type.GetType("System.String")),
                new DataColumn("Week 3", Type.GetType("System.String")),
                new DataColumn("Week 4", Type.GetType("System.String")),
                new DataColumn("Week 5", Type.GetType("System.String")),
                new DataColumn("Week 6", Type.GetType("System.String")),
                new DataColumn("Week 7", Type.GetType("System.String")),
                new DataColumn("Subject", Type.GetType("System.String")),
                new DataColumn("owner-subject", Type.GetType("System.String"))});

            //First Student
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Math", "0-Math" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "English", "0-English" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "History", "0-History" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Music", "0-Music" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Science", "0-Science" });

            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Math", "1-Math" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "English", "1-English" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "History", "1-History" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Music", "1-Music" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Science", "1-Science" });

            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Math", "2-Math" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "English", "2-English" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "History", "2-History" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Music", "2-Music" });
            fpgrades.Rows.Add(new Object[] { "76", "80", "85", "89", "90", "93", "99", "Science", "2-Science" });

            return fpgrades;
        }
    }
}
